{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/nceder/qpb4e/blob/main/code/Chapter%2023/Chapter_23.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 23 Saving Data"
      ],
      "metadata": {
        "id": "ZXgqm1JeyFKy"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 23.2 SQLite: Using the sqlite3 database"
      ],
      "metadata": {
        "id": "H-YHfY8VyRST"
      }
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9he6T8-F3A3a"
      },
      "source": [
        "import sqlite3\n",
        "conn = sqlite3.connect(\"datafile.db\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ATy8HkJI3A3b",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "21373567-e962-41b8-f0c9-c2d8ea5a0b1e"
      },
      "source": [
        "cursor = conn.cursor()\n",
        "cursor"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7bc273eeeb40>"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qo8DHqkb3A3c"
      },
      "source": [
        "cursor.execute(\"create table people (id integer primary key, name text, count integer)\")\n",
        "cursor.execute(\"insert into people (name, count) values ('Bob', 1)\")\n",
        "cursor.execute(\"insert into people (name, count) values (?, ?)\",\n",
        "               (\"Jill\", 15))\n",
        "conn.commit()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AvMIXGkY3A3c",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "cf6a3410-8ad6-451c-b3ca-c59ea06da998"
      },
      "source": [
        "cursor.execute(\"insert into people (name, count) values (:username, :usercount)\",\n",
        "                  {\"username\": \"Joe\", \"usercount\": 10})"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7bc273eeeb40>"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "b8z3sfGa3A3c",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7387dfad-f299-44db-c1a3-8f00dd9a06d2"
      },
      "source": [
        "result = cursor.execute(\"select * from people\")\n",
        "print(result.fetchall())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[(1, 'Bob', 1), (2, 'Jill', 15), (3, 'Joe', 10)]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ATjQD-MQ3A3c",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "5b9c9895-a03f-4ae5-a598-535b05b6fd51"
      },
      "source": [
        "result = cursor.execute(\"select * from people where name like :name\",\n",
        "                        {\"name\": \"bob\"})\n",
        "print(result.fetchall())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[(1, 'Bob', 1)]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MJhuhmQ23A3d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "53db3f0f-42a9-40c9-f4f6-d89d2d13ba27"
      },
      "source": [
        "cursor.execute(\"update people set count=? where name=?\", (20, \"Jill\"))\n",
        "result = cursor.execute(\"select * from people\")\n",
        "print(result.fetchall())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[(1, 'Bob', 1), (2, 'Jill', 20), (3, 'Joe', 10)]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "novIrTwn3A3d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8d50ecf8-e2a5-4701-f48b-fb377c823f83"
      },
      "source": [
        "result = cursor.execute(\"select * from people\")\n",
        "for row in result:\n",
        "    print(row)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Bob', 1)\n",
            "(2, 'Jill', 20)\n",
            "(3, 'Joe', 10)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UDi2VBLu3A3d"
      },
      "source": [
        "cursor.execute(\"update people set count=? where name=?\", (20, \"Jill\"))\n",
        "conn.commit()\n",
        "conn.close()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### TRY This: Creating and modifying Tables\n",
        "Using sqlite3, write the code that creates a database table for the Illinois weather data you loaded from a flat file in section 22.2. Suppose that you have similar data for more states and want to store more information about the states themselves. How could you modify your database to use a related table to store the state information?"
      ],
      "metadata": {
        "id": "joEnfxkgmT_o"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile temp_data_01.csv\n",
        "\"Notes\",\"State\",\"State Code\",\"Month Day, Year\",\"Month Day, Year Code\",Avg Daily Max Air Temperature (F),Record Count for Daily Max Air Temp (F),Min Temp for Daily Max Air Temp (F),Max Temp for Daily Max Air Temp (F),Avg Daily Max Heat Index (F),Record Count for Daily Max Heat Index (F),Min for Daily Max Heat Index (F),Max for Daily Max Heat Index (F),Daily Max Heat Index (F) % Coverage\n",
        "\n",
        ",\"Illinois\",\"17\",\"Jan 01, 1979\",\"1979/01/01\",17.48,994,6.00,30.50,Missing,0,Missing,Missing,0.00%\n",
        ",\"Illinois\",\"17\",\"Jan 02, 1979\",\"1979/01/02\",4.64,994,-6.40,15.80,Missing,0,Missing,Missing,0.00%\n",
        ",\"Illinois\",\"17\",\"Jan 03, 1979\",\"1979/01/03\",11.05,994,-0.70,24.70,Missing,0,Missing,Missing,0.00%\n",
        ",\"Illinois\",\"17\",\"Jan 04, 1979\",\"1979/01/04\",9.51,994,0.20,27.60,Missing,0,Missing,Missing,0.00%\n",
        ",\"Illinois\",\"17\",\"May 15, 1979\",\"1979/05/15\",68.42,994,61.00,75.10,Missing,0,Missing,Missing,0.00%\n",
        ",\"Illinois\",\"17\",\"May 16, 1979\",\"1979/05/16\",70.29,994,63.40,73.50,Missing,0,Missing,Missing,0.00%\n",
        ",\"Illinois\",\"17\",\"May 17, 1979\",\"1979/05/17\",75.34,994,64.00,80.50,82.60,2,82.40,82.80,0.20%\n",
        ",\"Illinois\",\"17\",\"May 18, 1979\",\"1979/05/18\",79.13,994,75.50,82.10,81.42,349,80.20,83.40,35.11%\n",
        ",\"Illinois\",\"17\",\"May 19, 1979\",\"1979/05/19\",74.94,994,66.90,83.10,82.87,78,81.60,85.20,7.85%\n"
      ],
      "metadata": {
        "id": "KlVzDUmPqBHr",
        "outputId": "237de17a-0815-486a-fde7-de5575e3d142",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing temp_data_01.csv\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "0ETjc2fImb7H"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title\n",
        "import sqlite3\n",
        "conn = sqlite3.connect(\"datafile.db\")\n",
        "\n",
        "cursor = conn.cursor()\n",
        "\n",
        "cursor.execute(\"\"\"create table weather (id integer primary key,\n",
        "              state text, state_code text,\n",
        "              year_text text, year_code text, avg_max_temp real,  max_temp_count integer,\n",
        "              max_temp_low real, max_temp_high real,\n",
        "              heat_index real, heat_index_count integer,\n",
        "              heat_index_low real, heat_index_high real,\n",
        "              heat_index_coverage text)\n",
        "              \"\"\")\n",
        "conn.commit()\n",
        "\n",
        "\n",
        "# load data from file\n",
        "import csv\n",
        "results = [fields[1:] for fields in csv.reader(open(\"temp_data_01.csv\", newline=''))]\n",
        "# write to database\n",
        "for row in results:\n",
        "  if row:\n",
        "      cursor.execute(\"\"\"insert into weather (state, state_code,\n",
        "              year_text, year_code, avg_max_temp,  max_temp_count,\n",
        "              max_temp_low, max_temp_high,\n",
        "              heat_index, heat_index_count,\n",
        "              heat_index_low, heat_index_high,\n",
        "              heat_index_coverage) values(?,?,?,?,?,?,?,?,?,?,?,?,?)\"\"\", row)\n",
        "conn.commit()"
      ],
      "metadata": {
        "cellView": "form",
        "id": "z0ZKXjHImcD-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 23.4 Making database handling easier with an ORM"
      ],
      "metadata": {
        "id": "ZRuIqyemzM_c"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 23.4.1 SQLAlchemy"
      ],
      "metadata": {
        "id": "0ddel9uKzU-g"
      }
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3-7otCBh3A3d"
      },
      "source": [
        "from sqlalchemy import create_engine, select, MetaData, Table, Column, Integer, String\n",
        "from sqlalchemy.orm import sessionmaker"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1UQh3NFu3A3e"
      },
      "source": [
        "dbPath = 'datafile2.db'\n",
        "engine = create_engine('sqlite:///%s' % dbPath)\n",
        "metadata = MetaData()\n",
        "people  = Table('people', metadata,\n",
        "                Column('id', Integer, primary_key=True),\n",
        "                Column('name', String),\n",
        "                Column('count', Integer),\n",
        "               )\n",
        "Session = sessionmaker(bind=engine)\n",
        "session = Session()\n",
        "metadata.create_all(engine)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LIvfhqLx3A3e",
        "outputId": "3aaef08f-40e0-4560-b742-bd3addf23f3d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "people_ins = people.insert().values(name='Bob', count=1)\n",
        "str(people_ins)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'INSERT INTO people (name, count) VALUES (:name, :count)'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x9_ij7Te3A3e",
        "outputId": "05a8f785-77e0-49e9-d8b0-fa82960c6b63",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "session.execute(people_ins)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlalchemy.engine.cursor.CursorResult at 0x7bc270258e20>"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0i-9kY583A3e"
      },
      "source": [
        "session.commit()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Oh74-8133A3e",
        "outputId": "3db6acd1-4010-410e-8e02-1b1df81a7071",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "session.execute(people_ins, [\n",
        "    {'name': 'Jill', 'count':15},\n",
        "    {'name': 'Joe', 'count':10}\n",
        "])"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlalchemy.engine.cursor.CursorResult at 0x7bc270258ca0>"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "avw5ESc03A3e",
        "outputId": "d08a3151-963a-4a8c-b26a-0a82c22ba84d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "session.commit()\n",
        "people_query = select(people)\n",
        "result = session.execute(people_query)\n",
        "for row in result:\n",
        "    print(row)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Bob', 1)\n",
            "(2, 'Jill', 15)\n",
            "(3, 'Joe', 10)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oGkT6cUf3A3e",
        "outputId": "3064148e-695f-4164-fa37-3004304dc1ce",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "result = session.execute(select(people).where(people.c.name == 'Jill'))\n",
        "for row in result:\n",
        "    print(row)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(2, 'Jill', 15)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MdTJF_ro3A3f",
        "outputId": "a0574964-3292-4c01-9e12-badeee4eee4e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "result = session.execute(people.update().values(count=20).where (people.c.name == 'Jill'))\n",
        "session.commit()\n",
        "result = session.execute(select(people).where(people.c.name == 'Jill'))\n",
        "for row in result:\n",
        "    print(row)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(2, 'Jill', 20)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Mapping table objects to classes"
      ],
      "metadata": {
        "id": "OAWyBHqv-VOm"
      }
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gzNB37cx3A3f",
        "outputId": "cf1e308a-67bf-446a-af0c-bfb855581be5",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "from sqlalchemy.orm import declarative_base\n",
        "Base = declarative_base()\n",
        "class People(Base):\n",
        "    __tablename__ = \"people\"\n",
        "    id = Column(Integer, primary_key=True)\n",
        "    name = Column(String)\n",
        "    count = Column(Integer)\n",
        "\n",
        "results = session.query(People).filter_by(name='Jill')\n",
        "for person in results:\n",
        "    print(person.id, person.name, person.count)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2 Jill 20\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "H30yE6IN3A3f",
        "outputId": "26232f87-ec3b-41bd-f532-f0211958c98f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "new_person = People(name='Jane', count=5)\n",
        "session.add(new_person)\n",
        "session.commit()\n",
        "results = session.query(People).all()\n",
        "for person in results:\n",
        "    print(person.id, person.name, person.count)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1 Bob 1\n",
            "2 Jill 20\n",
            "3 Joe 10\n",
            "4 Jane 5\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sKH8_Vwh3A3f",
        "outputId": "5fadbf8f-f534-4d30-a7a9-26844427eb2b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "jill = session.query(People).filter_by(name='Jill').first()\n",
        "jill.name"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'Jill'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_4JGuxvN3A3f",
        "outputId": "84aa76d5-dd71-4ff6-84e6-a2e017c4774c",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "jill.count = 22\n",
        "session.add(jill)\n",
        "session.commit()\n",
        "results = session.query(People).all()\n",
        "for person in results:\n",
        "    print(person.id, person.name, person.count)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1 Bob 1\n",
            "2 Jill 22\n",
            "3 Joe 10\n",
            "4 Jane 5\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "P2l5cOVL3A3g",
        "outputId": "b6e75111-5891-4300-9373-6a63fb1849d5",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "jane = session.query(People).filter_by(name='Jane').first()\n",
        "session.delete(jane)\n",
        "session.commit()\n",
        "jane = session.query(People).filter_by(name='Jane').first()\n",
        "print(jane)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "None\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Try This: Using an ORM\n",
        "Using the database from earlier, write an SQLAlchemy class to map to the data table, and use it to read the records from the table."
      ],
      "metadata": {
        "id": "RP4uOKYVnAKJ"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "N0tHEav6nIQo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title\n",
        "from sqlalchemy import create_engine, select, MetaData, Table, Column, Integer, String, Float\n",
        "from sqlalchemy.orm import sessionmaker\n",
        "dbPath = 'datafile.db'\n",
        "engine = create_engine('sqlite:///%s' % dbPath)\n",
        "metadata = MetaData()\n",
        "Session = sessionmaker(bind=engine)\n",
        "session = Session()\n",
        "metadata.create_all(engine)\n",
        "weather  = Table('weather', metadata,\n",
        "                Column('id', Integer, primary_key=True),\n",
        "                Column(\"state\", String),\n",
        "                Column(\"state_code\", String),\n",
        "                Column(\"year_text\", String ),\n",
        "                Column(\"year_code\", String),\n",
        "                Column(\"avg_max_temp\", Float),\n",
        "                Column(\"max_temp_count\", Integer),\n",
        "                Column(\"max_temp_low\", Float),\n",
        "                Column(\"max_temp_high\", Float),\n",
        "                Column(\"heat_index\", Float),\n",
        "                Column(\"heat_index_count\", Integer),\n",
        "                Column(\"heat_index_low\", Float),\n",
        "                Column(\"heat_index_high\", Float),\n",
        "                Column(\"heat_index_coverage\", String)\n",
        "                )\n",
        "Session = sessionmaker(bind=engine)\n",
        "session = Session()\n",
        "result = session.query(weather).all()\n",
        "for row in result:\n",
        "    print(row)"
      ],
      "metadata": {
        "cellView": "form",
        "id": "aYmRLxpNnIUi"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 23.4.2 Using Alembic for database schema changes"
      ],
      "metadata": {
        "id": "BVq8mxKT7pbc"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "! rm -rf alembic/"
      ],
      "metadata": {
        "id": "DAmbimejl_24"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wjLqSo9k3A3g",
        "outputId": "31d9ff0b-2138-41d1-e959-ee777c2f2aa0",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "! pip install alembic\n",
        "! alembic init alembic"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting alembic\n",
            "  Downloading alembic-1.13.3-py3-none-any.whl.metadata (7.4 kB)\n",
            "Requirement already satisfied: SQLAlchemy>=1.3.0 in /usr/local/lib/python3.10/dist-packages (from alembic) (2.0.35)\n",
            "Collecting Mako (from alembic)\n",
            "  Downloading Mako-1.3.5-py3-none-any.whl.metadata (2.9 kB)\n",
            "Requirement already satisfied: typing-extensions>=4 in /usr/local/lib/python3.10/dist-packages (from alembic) (4.12.2)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.10/dist-packages (from SQLAlchemy>=1.3.0->alembic) (3.1.1)\n",
            "Requirement already satisfied: MarkupSafe>=0.9.2 in /usr/local/lib/python3.10/dist-packages (from Mako->alembic) (2.1.5)\n",
            "Downloading alembic-1.13.3-py3-none-any.whl (233 kB)\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m233.2/233.2 kB\u001b[0m \u001b[31m1.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hDownloading Mako-1.3.5-py3-none-any.whl (78 kB)\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m78.6/78.6 kB\u001b[0m \u001b[31m5.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: Mako, alembic\n",
            "Successfully installed Mako-1.3.5 alembic-1.13.3\n",
            "Creating directory '/content/alembic' ...  done\n",
            "Creating directory '/content/alembic/versions' ...  done\n",
            "Generating /content/alembic/env.py ...  done\n",
            "Generating /content/alembic/script.py.mako ...  done\n",
            "Generating /content/alembic/README ...  done\n",
            "Generating /content/alembic.ini ...  done\n",
            "Please edit configuration/connection/logging settings in '/content/alembic.ini' before proceeding.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dptVIRNE3A3g"
      },
      "source": [
        "# This cell will update the alembic.ini file\n",
        "\n",
        "! sed -i 's/driver:\\/\\/user:pass@localhost\\/dbname/sqlite:\\/\\/\\/datafile.db/' alembic.ini"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# This cell creates the first revision script\n",
        "result = ! alembic revision -m \"create an address table\"\n",
        "filename= result[0].replace('Generating ', \"\").replace(\" ...  done\",\"\")\n",
        "version = filename.split(\"/\")[-1].split(\"_\")[0]"
      ],
      "metadata": {
        "id": "xICjY8aMdKk8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# This cell updates the revision script's upgrade() and downgrade() functions\n",
        "upgrade_cmd = \"\"\"def upgrade() -> None:\n",
        "    op.create_table(\n",
        "        'address',\n",
        "        sa.Column('id', sa.Integer, primary_key=True),\n",
        "        sa.Column('address', sa.String(50), nullable=False),\n",
        "        sa.Column('city', sa.String(50), nullable=False),\n",
        "        sa.Column('state', sa.String(20), nullable=False),\n",
        "    )\n",
        "\"\"\"\n",
        "downgrade_cmd = \"\"\"def downgrade() -> None:\n",
        "    op.drop_table('address')\"\"\"\n",
        "\n",
        "version_file = open(filename).read()\n",
        "version_file = version_file.replace(\"\"\"def upgrade() -> None:\n",
        "    pass\"\"\", upgrade_cmd)\n",
        "version_file = version_file.replace(\"\"\"def downgrade() -> None:\n",
        "    pass\"\"\", downgrade_cmd)\n",
        "print(version_file)\n",
        "\n",
        "open(filename, \"w\").write(version_file)"
      ],
      "metadata": {
        "id": "9kv5G7AF8rtS",
        "outputId": "7b1b6619-625b-415a-81d5-27edb5ca466d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\"\"\"create an address table\n",
            "\n",
            "Revision ID: def74d4e1932\n",
            "Revises: \n",
            "Create Date: 2024-09-27 02:02:49.374125\n",
            "\n",
            "\"\"\"\n",
            "from typing import Sequence, Union\n",
            "\n",
            "from alembic import op\n",
            "import sqlalchemy as sa\n",
            "\n",
            "\n",
            "# revision identifiers, used by Alembic.\n",
            "revision: str = 'def74d4e1932'\n",
            "down_revision: Union[str, None] = None\n",
            "branch_labels: Union[str, Sequence[str], None] = None\n",
            "depends_on: Union[str, Sequence[str], None] = None\n",
            "\n",
            "\n",
            "def upgrade() -> None:\n",
            "    op.create_table(\n",
            "        'address',\n",
            "        sa.Column('id', sa.Integer, primary_key=True),\n",
            "        sa.Column('address', sa.String(50), nullable=False),\n",
            "        sa.Column('city', sa.String(50), nullable=False),\n",
            "        sa.Column('state', sa.String(20), nullable=False),\n",
            "    )\n",
            "\n",
            "\n",
            "\n",
            "def downgrade() -> None:\n",
            "    op.drop_table('address')\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "771"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "for table in metadata.sorted_tables:\n",
        "    print(table.name)"
      ],
      "metadata": {
        "id": "QNp_zDe2izzZ",
        "outputId": "d9cd7290-428f-4051-cef9-985b696de18f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "people\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "! alembic upgrade head"
      ],
      "metadata": {
        "id": "Naaaeer_jJ0H",
        "outputId": "657fbf42-5d21-4233-8978-731dafe17f6f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "INFO  [alembic.runtime.migration] Context impl SQLiteImpl.\n",
            "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "metadata.sorted_tables"
      ],
      "metadata": {
        "id": "9dNnoNtVAKv7",
        "outputId": "071e0735-0c29-4ac4-fac2-7fad71227e73",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Table('people', MetaData(), Column('id', Integer(), table=<people>, primary_key=True, nullable=False), Column('name', String(), table=<people>), Column('count', Integer(), table=<people>), schema=None)]"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "Session = sessionmaker(bind=engine)\n",
        "session = Session()\n"
      ],
      "metadata": {
        "id": "yGzR4cUFCcSZ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YvMKja_z3A3g"
      },
      "source": [
        "metadata.reflect(engine)\n",
        "metadata.tables.keys()\n",
        "session.commit()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4P941qVM3A3g",
        "outputId": "0df0bc60-745c-4fc3-9e92-02003b748602",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "! alembic downgrade -1\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "INFO  [alembic.runtime.migration] Context impl SQLiteImpl.\n",
            "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n",
            "INFO  [alembic.runtime.migration] Running downgrade def74d4e1932 -> , create an address table\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "for table in metadata.sorted_tables:\n",
        "    print(table.name)"
      ],
      "metadata": {
        "id": "znJSPg0tOeeE",
        "outputId": "09a4c722-975d-4925-e0ea-b574cf4349ba",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "people\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 23.6 key:value stores with Redis"
      ],
      "metadata": {
        "id": "86oyCEiDxqZy"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install redis"
      ],
      "metadata": {
        "id": "4KRXJjgCpnmf",
        "outputId": "4202f540-5225-4d4d-add4-d7d191e4ba60",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: redis in /usr/local/lib/python3.10/dist-packages (5.2.0)\n",
            "Requirement already satisfied: async-timeout>=4.0.3 in /usr/local/lib/python3.10/dist-packages (from redis) (4.0.3)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### NOTE: You must create an account at redis.io or on another redis server for the examples to work properly.\n",
        "\n",
        "Be sure to use the correct hostname and password for your account in cell below."
      ],
      "metadata": {
        "id": "5RQEAQo5QCwf"
      }
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gMH0maR33A3h"
      },
      "source": [
        "import redis\n",
        "\n",
        "r = redis.Redis(\n",
        "    # set your host here\n",
        "    host='',\n",
        "    port=10032,\n",
        "    # set your password below\n",
        "    password='')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Basic operations"
      ],
      "metadata": {
        "id": "iM5wrNK4GgC5"
      }
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K1DROZCm3A3h",
        "outputId": "e568c005-9596-405a-abb3-e192dac0c295",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.keys()"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Array operations"
      ],
      "metadata": {
        "id": "whuJ6RdJGj96"
      }
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fhpG2vEu3A3h",
        "outputId": "6e8751e7-e6b2-4e0a-a8d8-ad1d2abc5bc0",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.set('a_key', 'my value')"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "True"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-TQM_EI63A3h",
        "outputId": "4f9a10ea-f01c-4438-a780-08c9cdd4b5da",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.keys()"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[b'a_key']"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1I-goUO53A3h",
        "outputId": "a9a16778-20e3-4958-cae4-0269bd995afd",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "v = r.get('a_key')\n",
        "v"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "b'my value'"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xVp_Bo7K3A3h",
        "outputId": "b12e8969-2e38-4f01-d763-57196849f827",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.incr('counter')"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SlSR6Q-w3A3h",
        "outputId": "10c675a3-ec5d-40d6-b8e7-eeff922c4dbb",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.get('counter')"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "b'1'"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "z7_zJ_6K3A3h",
        "outputId": "93b81124-0551-49db-9e3b-5c472b5b9968",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.incr('counter')"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "2"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mH4K8Ov33A3i",
        "outputId": "556c55c5-c404-4f45-917e-3de3ee23cff4",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.get('counter')"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "b'2'"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "l6VOd4Qk3A3i",
        "outputId": "cfc0d768-1700-4f25-e8b6-d5e08f53f64b",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.rpush(\"words\", \"one\")"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UrgiFhCy3A3i",
        "outputId": "80d52185-60f9-49a0-b70f-dd5563c2d22c",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.rpush(\"words\", \"two\")"
      ],
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "2"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "b86V4A-t3A3i",
        "outputId": "0b494705-03f8-4f30-db0e-14052dafb07e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.lrange(\"words\", 0, -1)"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[b'one', b'two']"
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sCsoIoM63A3i",
        "outputId": "b168edfe-7d55-42c0-b19c-e966d251b295",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.rpush(\"words\", \"three\")"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "3"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "R6SYq4n23A3i",
        "outputId": "4b4bbf63-cfe6-4792-9eb1-5fba23043fbb",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.lrange(\"words\", 0, -1)"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[b'one', b'two', b'three']"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "t6c3p64R3A3j",
        "outputId": "c666dd14-5cfc-4d57-99c4-0d2d367382f3",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.llen(\"words\")"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "3"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vdLIZMaX3A3j",
        "outputId": "bddb3fb0-b288-45c0-8e38-95e3f7e3b39d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.lpush(\"words\", \"zero\")"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "4"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Vcu54eX83A3j",
        "outputId": "94c8be18-5456-4e35-cdd1-800c25c92471",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.lrange(\"words\", 0, -1)"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[b'zero', b'one', b'two', b'three']"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kF4t7ge-3A3j",
        "outputId": "8026515e-4d7a-4e03-e786-d4c3dc33e207",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.lrange(\"words\", 2, 2)"
      ],
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[b'two']"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zakzEK8V3A3j",
        "outputId": "a06cacc7-4e0d-444d-96a9-af203ef90135",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.lindex(\"words\", 1)"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "b'one'"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3Ckv1Sbp3A3j",
        "outputId": "2a0b623b-615c-4fc2-df7e-bbc56c3826fc",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.lindex(\"words\", 2)"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "b'two'"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Expiration of values"
      ],
      "metadata": {
        "id": "4-iOzjGsGrJh"
      }
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PfTR7Ixp3A3j",
        "outputId": "9727ed39-3efb-4273-d021-9ca04c88f2bf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.setex(\"timed\", 10,  \"10 seconds\")"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "True"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Puzppjcm3A3k",
        "outputId": "effbde9c-7dfd-40ea-f085-6a51610eaade",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.pttl(\"timed\")"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "9019"
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z1tRvYUL3A3k",
        "outputId": "2e2a6d33-090a-4c7d-934e-290a2c75fe3f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "r.pttl(\"timed\")"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "6720"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0DenF6k93A3k",
        "outputId": "8dc215df-c828-4088-ffc0-e49fc4ac00f0",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "b\"timed\" in r.keys()"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "True"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Quick Check: Uses of Key:Value stores\n",
        "What sorts of data and applications would benefit most from a key:value store like Redis?\n",
        "\n",
        "* Quick lookup of data\n",
        "* Caching\n"
      ],
      "metadata": {
        "id": "aTD4x8ZJ5vV2"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 23.7 Documents in MongoDB"
      ],
      "metadata": {
        "id": "JoNxPeYW0b1v"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "! pip install pymongo"
      ],
      "metadata": {
        "id": "Dw5nUkwa0iB7",
        "outputId": "afceca2d-38c7-4dfa-f42b-3cca29622496",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting pymongo\n",
            "  Downloading pymongo-4.10.1-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (22 kB)\n",
            "Collecting dnspython<3.0.0,>=1.16.0 (from pymongo)\n",
            "  Downloading dnspython-2.7.0-py3-none-any.whl.metadata (5.8 kB)\n",
            "Downloading pymongo-4.10.1-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.4 MB)\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.4/1.4 MB\u001b[0m \u001b[31m25.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hDownloading dnspython-2.7.0-py3-none-any.whl (313 kB)\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m313.6/313.6 kB\u001b[0m \u001b[31m21.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: dnspython, pymongo\n",
            "Successfully installed dnspython-2.7.0 pymongo-4.10.1\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### NOTE: you must have an account at mongondb.net or on another Mongo server for this code to work properly.\n",
        "\n",
        "You may also have to authorize Access from Anywhere to connect to your DB."
      ],
      "metadata": {
        "id": "fYM3KhrZRjkD"
      }
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7BOhlvK63A3k"
      },
      "source": [
        "from pymongo import MongoClient\n",
        "client = MongoClient(host='*** connection string here ***')   #A"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mnrAoXTH3A3k",
        "outputId": "ae0df934-4db3-4871-9da0-2635d9154b43",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "import datetime\n",
        "a_document = {'name': 'Jane',\n",
        "              'age': 34,\n",
        "              'interests': ['Python', 'databases', 'statistics'],\n",
        "              'date_added': datetime.datetime.now()\n",
        "}\n",
        "db = client.my_data     #A\n",
        "collection = db.docs   #B\n",
        "result = collection.find_one()  #C\n",
        "db.list_collection_names()"
      ],
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['docs']"
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "collection.insert_one(a_document)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NQClxNNTxmTz",
        "outputId": "233cb7cd-4b68-4287-fd61-111329a4449a"
      },
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "InsertOneResult(ObjectId('674933fd4b145489056d9af8'), acknowledged=True)"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BDqFFwgo3A3l",
        "outputId": "83f072cf-ba61-4023-f42f-c3d8e300cf56",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "collection.find_one()    #A"
      ],
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'_id': ObjectId('674933fd4b145489056d9af8'),\n",
              " 'name': 'Jane',\n",
              " 'age': 34,\n",
              " 'interests': ['Python', 'databases', 'statistics'],\n",
              " 'date_added': datetime.datetime(2024, 11, 29, 3, 24, 32, 765000)}"
            ]
          },
          "metadata": {},
          "execution_count": 37
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZWKkXY103A3l",
        "outputId": "285c3bd1-72b4-4eb1-9a75-028067008e68",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "from bson.objectid import ObjectId\n",
        "\n",
        "###----->>>> use ObjectId from cell above in code below\n",
        "\n",
        "collection.find_one({\"_id\":ObjectId('674933fd4b145489056d9af8')})  #B\n"
      ],
      "execution_count": 43,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'_id': ObjectId('674933fd4b145489056d9af8'),\n",
              " 'name': 'Jane',\n",
              " 'age': 34,\n",
              " 'interests': ['Python', 'databases', 'statistics'],\n",
              " 'date_added': datetime.datetime(2024, 11, 29, 3, 24, 32, 765000)}"
            ]
          },
          "metadata": {},
          "execution_count": 43
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rZgi0WtU3A3l",
        "outputId": "0d9547e9-ff28-42fd-8463-cc84be48898f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# use ObjectId from above in code below\n",
        "collection.update_one({\"_id\":ObjectId('674933fd4b145489056d9af8')}, {\"$set\": {\"name\":\"Ann\"}})       #C"
      ],
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "UpdateResult({'n': 1, 'electionId': ObjectId('7fffffff00000000000001a6'), 'opTime': {'ts': Timestamp(1732850721, 33), 't': 422}, 'nModified': 1, 'ok': 1.0, '$clusterTime': {'clusterTime': Timestamp(1732850721, 33), 'signature': {'hash': b'd{\\xcc\\xd7\\xe5\\xf3}T\\xe0\\x84\"x\\xd8\\x04x+\\xaa\\x15T\\xf7', 'keyId': 7394931654956941332}}, 'operationTime': Timestamp(1732850721, 33), 'updatedExisting': True}, acknowledged=True)"
            ]
          },
          "metadata": {},
          "execution_count": 44
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "u4573BhP3A3l"
      },
      "source": [
        "# use ObjectId from above in code below\n",
        "collection.find_one({\"_id\":ObjectId('66f616d8b15e1d493ca8b8d0')})"
      ],
      "execution_count": 40,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tgOuoGVd3A3m",
        "outputId": "22585e2a-ef03-4719-a070-0bf94974bc1d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# use ObjectId from above in code below\n",
        "collection.replace_one({\"_id\":ObjectId('674933fd4b145489056d9af8')}, {\"name\":\"Maria\"})                 #D"
      ],
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "UpdateResult({'n': 1, 'electionId': ObjectId('7fffffff00000000000001a6'), 'opTime': {'ts': Timestamp(1732850725, 34), 't': 422}, 'nModified': 1, 'ok': 1.0, '$clusterTime': {'clusterTime': Timestamp(1732850725, 34), 'signature': {'hash': b'g\\x82q\\xf2J\\xad\\xeeXG\\xed\\r\\x8atO\\xbf\\xe5\\xc0\\x90\\x9e ', 'keyId': 7394931654956941332}}, 'operationTime': Timestamp(1732850725, 34), 'updatedExisting': True}, acknowledged=True)"
            ]
          },
          "metadata": {},
          "execution_count": 45
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MS6fduWg3A3m",
        "outputId": "c4818e99-e7c9-4280-8e8d-f32c1e1daba9",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# use ObjectId from above in code below\n",
        "collection.find_one({\"_id\":ObjectId('674933fd4b145489056d9af8')})"
      ],
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'_id': ObjectId('674933fd4b145489056d9af8'), 'name': 'Maria'}"
            ]
          },
          "metadata": {},
          "execution_count": 46
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mR4ld8dy3A3m",
        "outputId": "f384bc99-767b-445f-a9af-094399b584ae",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# use ObjectId from above in code below\n",
        "collection.delete_one({\"_id\":ObjectId('674933fd4b145489056d9af8')}) #E"
      ],
      "execution_count": 47,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DeleteResult({'n': 1, 'electionId': ObjectId('7fffffff00000000000001a6'), 'opTime': {'ts': Timestamp(1732850731, 39), 't': 422}, 'ok': 1.0, '$clusterTime': {'clusterTime': Timestamp(1732850731, 39), 'signature': {'hash': b\"\\xa7\\xcf7g\\x89}\\xfc\\xa2\\x97\\xc44\\xa9V ']j\\x0f\\x1fn\", 'keyId': 7394931654956941332}}, 'operationTime': Timestamp(1732850731, 39)}, acknowledged=True)"
            ]
          },
          "metadata": {},
          "execution_count": 47
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lbgUmf1E3A3m"
      },
      "source": [
        "collection.find_one()"
      ],
      "execution_count": 48,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SkkSli0j3A3m",
        "outputId": "0c83babb-35de-4ecd-cd9f-6b8ae5a87c16",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "db.list_collection_names()"
      ],
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['docs']"
            ]
          },
          "metadata": {},
          "execution_count": 49
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lbMFRqdV3A3m",
        "outputId": "a6b1e820-949f-4480-b8e3-f2fa6580972a",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "collection.drop()\n",
        "db.list_collection_names()"
      ],
      "execution_count": 50,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Quick Check: Uses of MONGODB\n",
        "Thinking back over the various data samples you’ve seen so far and other types of data in your experience, which do you think would be well suited to being stored in a database like MongoDB? Would others clearly not be suited, and if so, why not?\n",
        "\n",
        "#### Discussion\n",
        "Data that comes in large and/or more loosely organized chunks is suited to MongoDB, such as the contents of a web page or document.\n",
        "\n",
        "Data with a specific structure is better suited to relational data. The weather data you've seen is a good example."
      ],
      "metadata": {
        "id": "xBVBIK4r6NXt"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Lab 23 Create a database\n",
        "\n",
        "This is an open-ended challenge, so there is no “official” answer provided."
      ],
      "metadata": {
        "id": "wfi0lCDC6dXX"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "QXty10oR6n7r"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}